﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Lib.Common;
using Microsoft.ApplicationBlocks.Data;
using Module.EntitiesInfo;

namespace Module.DataAccess
{
    public class CMSContentsDB
    {
        #region ConnectionString
        private string _connectionString;
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }
        public CMSContentsDB()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["localsql"].ToString();
        }
        #endregion

        #region Methods

        public virtual int Insert(CMSContentsInfo cMSContentsInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ContentLangID", cMSContentsInfo.ContentLangID),
				new SqlParameter("@ContentVersionID", cMSContentsInfo.ContentVersionID),
				new SqlParameter("@VersionID", cMSContentsInfo.VersionID),
				new SqlParameter("@ZoneID", cMSContentsInfo.ZoneID),
				new SqlParameter("@Headline", cMSContentsInfo.Headline),
				new SqlParameter("@SubHeadline", cMSContentsInfo.SubHeadline),
				new SqlParameter("@Teaser", cMSContentsInfo.Teaser),
				new SqlParameter("@Avatar", cMSContentsInfo.Avatar),
				new SqlParameter("@AvatarTeaser", cMSContentsInfo.AvatarTeaser),
				new SqlParameter("@SmallAvatar", cMSContentsInfo.SmallAvatar),
				new SqlParameter("@BigAvatar", cMSContentsInfo.BigAvatar),
				new SqlParameter("@AttachFile", cMSContentsInfo.AttachFile),
				new SqlParameter("@Status", cMSContentsInfo.Status),
				new SqlParameter("@Body", cMSContentsInfo.Body),
				new SqlParameter("@PrimaryAuthor", cMSContentsInfo.PrimaryAuthor),
				new SqlParameter("@SecondaryAuthors", cMSContentsInfo.SecondaryAuthors),
				new SqlParameter("@CreatedBy", cMSContentsInfo.CreatedBy),
				new SqlParameter("@CreatedOn", cMSContentsInfo.CreatedOn),
				new SqlParameter("@ModifiedBy", cMSContentsInfo.ModifiedBy),
				new SqlParameter("@ModifiedOn", cMSContentsInfo.ModifiedOn),
				new SqlParameter("@DeletedOn", cMSContentsInfo.DeletedOn),
				new SqlParameter("@DeletedBy", cMSContentsInfo.DeletedBy),
				new SqlParameter("@DatetimePublish", cMSContentsInfo.DatetimePublish),
				new SqlParameter("@UserPublish", cMSContentsInfo.UserPublish),
				new SqlParameter("@DatetimeRemove", cMSContentsInfo.DatetimeRemove),
				new SqlParameter("@UserRemove", cMSContentsInfo.UserRemove),
				new SqlParameter("@ShowType", cMSContentsInfo.ShowType),
				new SqlParameter("@Lang", cMSContentsInfo.Lang),
				new SqlParameter("@HitCount", cMSContentsInfo.HitCount),
				new SqlParameter("@URL", cMSContentsInfo.URL),
				new SqlParameter("@KeyContents", cMSContentsInfo.KeyContents),
				new SqlParameter("@ReturnBody", cMSContentsInfo.ReturnBody),
				new SqlParameter("@Locked", cMSContentsInfo.Locked),
				new SqlParameter("@HitCountComments", cMSContentsInfo.HitCountComments),
				new SqlParameter("@Author", cMSContentsInfo.Author),
				new SqlParameter("@Check_type", cMSContentsInfo.Check_type)
			};

            return cMSContentsInfo.ContentID = (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_Insert", parameters);
        }
   
        public virtual void Update(CMSContentsInfo cMSContentsInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ContentID", cMSContentsInfo.ContentID),
				new SqlParameter("@ContentLangID", cMSContentsInfo.ContentLangID),
				new SqlParameter("@ContentVersionID", cMSContentsInfo.ContentVersionID),
				new SqlParameter("@VersionID", cMSContentsInfo.VersionID),
				new SqlParameter("@ZoneID", cMSContentsInfo.ZoneID),
				new SqlParameter("@Headline", cMSContentsInfo.Headline),
				new SqlParameter("@SubHeadline", cMSContentsInfo.SubHeadline),
				new SqlParameter("@Teaser", cMSContentsInfo.Teaser),
				new SqlParameter("@Avatar", cMSContentsInfo.Avatar),
				new SqlParameter("@AvatarTeaser", cMSContentsInfo.AvatarTeaser),
				new SqlParameter("@SmallAvatar", cMSContentsInfo.SmallAvatar),
				new SqlParameter("@BigAvatar", cMSContentsInfo.BigAvatar),
				new SqlParameter("@AttachFile", cMSContentsInfo.AttachFile),				
				new SqlParameter("@Body", cMSContentsInfo.Body),
				new SqlParameter("@PrimaryAuthor", cMSContentsInfo.PrimaryAuthor),
				new SqlParameter("@SecondaryAuthors", cMSContentsInfo.SecondaryAuthors),				
				new SqlParameter("@ModifiedBy", cMSContentsInfo.ModifiedBy),
				new SqlParameter("@ModifiedOn", cMSContentsInfo.ModifiedOn),
				new SqlParameter("@DeletedOn", cMSContentsInfo.DeletedOn),
				new SqlParameter("@DeletedBy", cMSContentsInfo.DeletedBy),
				new SqlParameter("@DatetimePublish", cMSContentsInfo.DatetimePublish),
				new SqlParameter("@UserPublish", cMSContentsInfo.UserPublish),
				new SqlParameter("@DatetimeRemove", cMSContentsInfo.DatetimeRemove),
				new SqlParameter("@UserRemove", cMSContentsInfo.UserRemove),
				new SqlParameter("@ShowType", cMSContentsInfo.ShowType),
				new SqlParameter("@Lang", cMSContentsInfo.Lang),
				new SqlParameter("@HitCount", cMSContentsInfo.HitCount),
				new SqlParameter("@URL", cMSContentsInfo.URL),
				new SqlParameter("@KeyContents", cMSContentsInfo.KeyContents),
				new SqlParameter("@ReturnBody", cMSContentsInfo.ReturnBody),
				new SqlParameter("@Locked", cMSContentsInfo.Locked),
				new SqlParameter("@HitCountComments", cMSContentsInfo.HitCountComments),
				new SqlParameter("@Author", cMSContentsInfo.Author),
				new SqlParameter("@Check_type", cMSContentsInfo.Check_type)
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_Update", parameters);
        }

    
        public virtual void Delete(int contentID)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ContentID", contentID)
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_Delete", parameters);
        }

        public void UpdateStatus(string contentID, int check)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@ContentID", contentID),
                new SqlParameter("@check", check)
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_UpdateStatus", parameters);
        }
        
     
    
        public DataTable SelectSearchAll(int status, int lang, int zoneId, string keyword, int createBy, System.DateTime startDate, System.DateTime endDate, int from, int to)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Status",status), 
                new SqlParameter("@lang",lang),
                new SqlParameter("@ZoneID",zoneId),
                new SqlParameter("@Keyword",keyword),
                new SqlParameter("@CreatedBy",createBy) ,
                new SqlParameter("@StartDate",startDate),
                new SqlParameter("@EndDate",endDate),
                new SqlParameter("@From",from),
                new SqlParameter("@To",to)
            }
            ;
            using (DataTable dt = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_SearchAll", parameters).Tables[0])
            {
                if (dt.Rows.Count > 0)
                    return dt;
                else
                {
                    return null;
                }
            }
        }

        public IDataReader Select(int? type, string para_1, string para_2)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "CMS_Contents_Select", type, para_1, para_2);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public IDataReader Select(int? type, string para_1)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "CMS_Contents_Select", type, para_1);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public IDataReader Select(int? type)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "CMS_Contents_Select", type);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type, string para_1, string para_2)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "CMS_Contents_Select", type, para_1, para_2).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type, string para_1)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "CMS_Contents_Select", type, para_1).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "CMS_Contents_Select", type).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }

        public virtual DataTable GetContentTypeLates(int lang, int topN, int zoneID, int status, string showType, int type)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@TopN", topN),
                new SqlParameter("@ZoneID", zoneID),
                new SqlParameter("@Status", status),
                new SqlParameter("@ShowType", showType),
                new SqlParameter("@lang", lang) ,
                new SqlParameter("@type", type)
			};
            using (DataTable dt = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_Type_Latest", parameters).Tables[0])
            {
                if (dt != null && dt.Rows.Count > 0)
                    return dt;
                else
                    return null;
            }
        }

        public virtual DataTable GetAll(int topN,int zoneID,int status,int param)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@TopN", topN),
                new SqlParameter("@ZoneID", zoneID),
                new SqlParameter("@Status", status),
                new SqlParameter("@Param", param),	
			   
			};
            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_GetAll", parameters).Tables[0];
        }
      
 
        public DataTable GetnewsOther(int zoneId, int status, int contentId)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@ZoneID",zoneId),
                new SqlParameter("@Status",status), 
                new SqlParameter("@ContentID",contentId) 
            }
            ;
            using (DataTable dt = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_NewsOther", parameters).Tables[0])
            {
                if (dt.Rows.Count > 0)
                    return dt;
                else
                {
                    return null;
                }
            }
        }

        public virtual int GetCountContent(int status, int lang, int zoneId, string keyword, int createBy, DateTime startDate,
                         DateTime endDate)
        {

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Status", status),
                new SqlParameter("@lang", lang),
                new SqlParameter("@ZoneID", zoneId),
                new SqlParameter("@Keyword", keyword),
                new SqlParameter("@Createdby", createBy),
                new SqlParameter("@StartDate", startDate),
                new SqlParameter("@EndDate", endDate)
            };
            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "CMS_Contents_GetTotal", parameters);
        }
        #endregion
    }
    
}

